package com.ideal.manage.esms.service.overview;

import com.alibaba.fastjson.JSONObject;
import com.ideal.manage.esms.bean.alarm.Alarm;
import com.ideal.manage.esms.bean.alarm.AlarmHistory;
import com.ideal.manage.esms.bean.alarm.QAlarm;
import com.ideal.manage.esms.bean.diagnostic.DiagnoseResultInfo;
import com.ideal.manage.esms.bean.diagnostic.QDiagnoseResultInfo;
import com.ideal.manage.esms.bean.esms.EsmsIntegration;
import com.ideal.manage.esms.bean.esms.QEsmsIntegration;
import com.ideal.manage.esms.bean.project.*;
import com.ideal.manage.esms.config.shiro.MyShiroRealm;
import com.ideal.manage.esms.jpa.JpaRepositoryImpl;
import com.ideal.manage.esms.service.diagnostic.DiagnoseResultInfoService;
import com.ideal.manage.esms.service.monitor.Utils;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.Tuple;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.PostConstruct;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.time.*;
import java.util.*;

@Service
@Transactional
public class OverviewService2 {
    private JpaRepositoryImpl<ProjectManage, String> projectManageRepository;
    private JpaRepositoryImpl<EquipmentManage, String> equipmentManageRepository;
    private JpaRepositoryImpl<DiagnoseResultInfo, String> diagnoseResultInfoRepository;
    private JpaRepositoryImpl<Alarm, String> alarmRepository;
    private JpaRepositoryImpl<EsmsIntegration, Long> integrationRepository;
    private JpaRepositoryImpl<StadningBookInfo, String> stadningBookInfoRepository;

    @Autowired
    private EntityManager entityManager;
    @Autowired
    private DiagnoseResultInfoService diagnoseResultInfoService;

    @PostConstruct
    public void init() {
        this.projectManageRepository = new JpaRepositoryImpl<>(ProjectManage.class, entityManager);
        this.equipmentManageRepository = new JpaRepositoryImpl<>(EquipmentManage.class, entityManager);
        this.diagnoseResultInfoRepository = new JpaRepositoryImpl<>(DiagnoseResultInfo.class, entityManager);
        this.alarmRepository = new JpaRepositoryImpl<>(Alarm.class, entityManager);
        this.integrationRepository = new JpaRepositoryImpl<EsmsIntegration, Long>(EsmsIntegration.class, entityManager);
        this.stadningBookInfoRepository = new JpaRepositoryImpl<StadningBookInfo, String>(StadningBookInfo.class, entityManager);
    }


    /**
     * 平台规模 总用户数
     *
     * @return
     */
    public Long getTotalUsers() {
        MyShiroRealm.ShiroUser shiroUser = (MyShiroRealm.ShiroUser) SecurityUtils.getSubject().getPrincipal();
        BooleanBuilder where = new BooleanBuilder();
        where.and(QProjectManage.projectManage.isValid.eq(1));
        if (shiroUser.isAdmin()) {
            where.and(QProjectManage.projectManage.id.in(shiroUser.getProjectIdSet()));
        }
        return projectManageRepository.count(where);
    }


    /**
     * 平台规模 中 的饼状图
     *
     * @return
     */
    public List<JSONObject> getPlatformScale() {
        ArrayList list = new ArrayList();
        MyShiroRealm.ShiroUser shiroUser = (MyShiroRealm.ShiroUser) SecurityUtils.getSubject().getPrincipal();
        BooleanBuilder where = new BooleanBuilder();
        where.and(QProjectManage.projectManage.isValid.eq(1));
        if (shiroUser.isAdmin()) {
            where.and(QProjectManage.projectManage.id.in(shiroUser.getProjectIdSet()));
        }
        JSONObject jsonObject = new JSONObject();
        //正常
        Long onCount = projectManageRepository.count(QProjectManage.projectManage.status.eq(1).and(where));
        jsonObject.put("value", onCount);
        jsonObject.put("name", "正常运行");
        if (onCount > 0) {
            list.add(jsonObject);
        }
        //离线
        jsonObject = new JSONObject();
        Long offCount = projectManageRepository.count(QProjectManage.projectManage.status.eq(0).and(where));
        jsonObject.put("value", offCount);
        jsonObject.put("name", "离线");
        if (offCount > 0) {
            list.add(jsonObject);
        }
        //待机
        jsonObject = new JSONObject();
        Long value = projectManageRepository.count(where) - onCount - offCount;
        jsonObject.put("value", value);
        jsonObject.put("name", "待机");
        if (value > 0) {
            list.add(jsonObject);
        }

        //故障

        return list;
    }

    protected List<String> getEquipmentManageIds() {
        QProjectManage qProjectManage = QProjectManage.projectManage;

        MyShiroRealm.ShiroUser shiroUser = (MyShiroRealm.ShiroUser) SecurityUtils.getSubject().getPrincipal();
        BooleanBuilder where = new BooleanBuilder();
        where.and(qProjectManage.isValid.eq(1));
        if (shiroUser.isAdmin()) {
            where.and(qProjectManage.id.in(shiroUser.getProjectIdSet()));
        }

        List<String> projectIds = projectManageRepository.query()
                .select(qProjectManage.id)
                .from(qProjectManage)
                .where(where)
                .fetch();
        QEquipmentManage qEquipmentManage = QEquipmentManage.equipmentManage;
        List<String> equipmentIds = equipmentManageRepository.query()
                .select(qEquipmentManage.id)
                .from(qEquipmentManage)
                .where(qEquipmentManage.project.id.in(projectIds)
                        .and(qEquipmentManage.isValid.eq(1)))
                .fetch();
        return equipmentIds;
    }

    /**
     * 平台性能 平均充放电效率
     *
     * @return
     */
    public Double getAverageChargeDischarge() {
        QProjectManage qProjectManage = QProjectManage.projectManage;
        MyShiroRealm.ShiroUser shiroUser = (MyShiroRealm.ShiroUser) SecurityUtils.getSubject().getPrincipal();
        BooleanBuilder where = new BooleanBuilder();
        where.and(qProjectManage.isValid.eq(1));
        if (shiroUser.isAdmin()) {
            where.and(qProjectManage.id.in(shiroUser.getProjectIdSet()));
        }

        List<String> projectIps = projectManageRepository.query()
                .select(qProjectManage.ip)
                .from(qProjectManage)
                .where(qProjectManage.status.eq(1).and(where))
                .fetch();
        Query qry = entityManager.createNativeQuery("SELECT COUNT(1) FROM (SELECT date(cal_time) AS dday FROM esms_integration GROUP BY dday) AS t");
        BigInteger totalDays = (BigInteger) qry.getSingleResult();

        QEsmsIntegration qEsmsIntegration = QEsmsIntegration.esmsIntegration;
        double jrfdl = Utils.toDouble(integrationRepository.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.type.eq("fdl").and(qEsmsIntegration.projectIp.in(projectIps)))
                .fetchOne());
        double jrcdl = Utils.toDouble(integrationRepository.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.type.eq("cdl").and(qEsmsIntegration.projectIp.in(projectIps)))
                .fetchOne());

        //放电量/充电量按天取平均值
        return jrcdl == 0 ? 0 : Math.abs(Utils.halfUp((jrfdl / jrcdl) / totalDays.longValue()));
    }


    /**
     * 平台性能 系统安全稳定运行
     *
     * @return
     */
    public Integer getSafeOperationDays() {
        List<String> ids = this.getEquipmentManageIds();

        List<Date> lastDate = alarmRepository.query().select(QAlarm.alarm.createTime)
                .from(QAlarm.alarm)
                .where(QAlarm.alarm.equipmentId.in(ids))
                .orderBy(QAlarm.alarm.createTime.desc())
                .limit(1)
                .fetch();
        int max = 0;
        for (Date date : lastDate) {
            Period period = Period.between(date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate(),
                    LocalDate.now());
            int days = period.getDays();
            if (days > max) {
                max = days;
            }
        }
        return max;
    }

    public JSONObject getPlatformBenefit() {
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("saveElectricity", "");  //累计节省电量
        jsonObject.put("saveBattery", "");  //累计再利用退役电池
        jsonObject.put("billPercent", "");  //用户平均电费降比率/收益率
        jsonObject.put("saveC", "");        //减少C排放
        jsonObject.put("saveCO2", "");      //减少CO2排放
        jsonObject.put("saveTree", "");     //等效种植树木
        jsonObject.put("saveMei", "");      //节省标准煤
        // 1. 找到所有的项目
        QProjectManage qProjectManage = QProjectManage.projectManage;
        MyShiroRealm.ShiroUser shiroUser = (MyShiroRealm.ShiroUser) SecurityUtils.getSubject().getPrincipal();
        BooleanBuilder where = new BooleanBuilder();
        where.and(qProjectManage.isValid.eq(1));
        if (shiroUser.isAdmin()) {
            where.and(qProjectManage.id.in(shiroUser.getProjectIdSet()));
        }

        List<String> projectIps = projectManageRepository
                .query()
                .select(qProjectManage.ip)
                .from(qProjectManage)
                .where(where)
                .fetch();
        // 2. 累计节省电量
        QEsmsIntegration qEsmsIntegration = QEsmsIntegration.esmsIntegration;
        Double saveElectricity = integrationRepository
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.in(projectIps).and(qEsmsIntegration.type.eq("fdl")))
                .fetchOne();

        // 3) 计算
        jsonObject.put("saveElectricity", new BigDecimal(saveElectricity / 1000.0).setScale(2, BigDecimal.ROUND_HALF_UP));  //累计节省电量
        // 2. 累计再利用退役电池
        List<String> equipmentIds = this.getEquipmentManageIds();
        QDiagnoseResultInfo qDiagnoseResultInfo = QDiagnoseResultInfo.diagnoseResultInfo;
        BigDecimal saveBattery = diagnoseResultInfoRepository.query()
                .select(qDiagnoseResultInfo.resultVal.castToNum(BigDecimal.class).sum())
                .from(qDiagnoseResultInfo)
                .where(qDiagnoseResultInfo.equipment.id.in(equipmentIds)
                        .and(qDiagnoseResultInfo.setVal.eq("kccdl")))
                .fetchOne();
        if (saveBattery == null) {
            saveBattery = new BigDecimal(0.0);
        }
        saveBattery = saveBattery.divide(new BigDecimal(130.0), 2, BigDecimal.ROUND_HALF_UP);
        jsonObject.put("saveBattery", saveBattery.setScale(2, BigDecimal.ROUND_HALF_UP));  //累计再利用退役电池
        // 3. 用户平均电费降比率/收益率
        Double sdf = integrationRepository.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.in(projectIps).and(qEsmsIntegration.type.in("sdf")))
                .groupBy(qEsmsIntegration.type)
                .fetchOne();
        if (sdf == null) {
            sdf = 0.0;
        }
        Double hdf = integrationRepository.query()
                .select(qEsmsIntegration.val.sum())
                .from(qEsmsIntegration)
                .where(qEsmsIntegration.projectIp.in(projectIps).and(qEsmsIntegration.type.in("hdf")))
                .groupBy(qEsmsIntegration.type)
                .fetchOne();

        if (hdf == null) {
            hdf = 0.0;
        }
        if (hdf == 0.0 || projectIps.size() == 0) {

            jsonObject.put("billPercent", 0.0);  //用户平均电费降比率/收益率
        } else {
            jsonObject.put("billPercent", (sdf / hdf) / projectIps.size());  //用户平均电费降比率/收益率
        }
        // 4. 剩下的
        jsonObject.put("saveC", saveBattery.multiply(new BigDecimal(0.0000272)).setScale(2, BigDecimal.ROUND_HALF_UP));        //减少C排放
        jsonObject.put("saveCO2", saveBattery.multiply(new BigDecimal(0.0000997)).setScale(2, BigDecimal.ROUND_HALF_UP));      //减少CO2排放
        jsonObject.put("saveTree", saveBattery.multiply(new BigDecimal(0.0000199)).setScale(2, BigDecimal.ROUND_HALF_UP));     //等效种植树木
        jsonObject.put("saveMei", saveBattery.multiply(new BigDecimal(0.0000404)).setScale(2, BigDecimal.ROUND_HALF_UP));      //节省标准煤
        return jsonObject;
    }

    public List<JSONObject> findProjectInfo() {
        List<JSONObject> result = new ArrayList<>();
        // 1. 找到所有台账对应的值
        QStadningBookInfo qStadningBookInfo = QStadningBookInfo.stadningBookInfo;
        List<StadningBookInfo> stadningBookInfos = stadningBookInfoRepository.query()
                .select(qStadningBookInfo)
                .from(qStadningBookInfo)
                .where(qStadningBookInfo.isValid.eq(1)
                        .and(qStadningBookInfo.account.parameterType.eq(1))
                        .and(qStadningBookInfo.project.isValid.eq(1))
                        .and(qStadningBookInfo.project.isNotNull()))
                .orderBy(qStadningBookInfo.project.id.asc())
                .fetch();
        // 2. 根据project来新建 jsonObject 放到 result结果集 里面
        String project = "";
        JSONObject jsonObject = null;
        for (StadningBookInfo stadningBookInfo : stadningBookInfos) {
            // 1) 判断项目是否有效
            if (stadningBookInfo.getProject().getIsValid() < 1) {    // 因为 s.project.isVaild=1 这个有空值就报错只能写这边了
                continue;
            }
            // 2) 判断是否需要新建项目
            String project_id = stadningBookInfo.getProject().getId();
            if (!project.equals(project_id)) {
                project = project_id;
                QEquipmentManage qEquipmentManage = QEquipmentManage.equipmentManage;
                List<String> equipmentIdList = equipmentManageRepository.query()
                        .select(qEquipmentManage.id)
                        .from(qEquipmentManage)
                        .where(QEquipmentManage.equipmentManage.project.id.eq(project_id))
                        .fetch();
                jsonObject = new JSONObject();
                jsonObject.put("id", stadningBookInfo.getProject().getId());
                jsonObject.put("projectName", stadningBookInfo.getProject().getName());
                jsonObject.put("longitude", 0.00);
                jsonObject.put("latitude", 0.00);
                jsonObject.put("totalStorageEnergy", "未知");
                jsonObject.put("safeRunningDays", "未知");
                jsonObject.put("totalElectricity", "未知");
                jsonObject.put("faults", "未知");
                jsonObject.put("contacts", "未知");
                jsonObject.put("telphone", "未知");
                //总存储能量=可储蓄电量
                QDiagnoseResultInfo StorageEnergy = QDiagnoseResultInfo.diagnoseResultInfo;
                BigDecimal totalStorageEnergy = diagnoseResultInfoRepository.query()
                        .select(StorageEnergy.resultVal.castToNum(BigDecimal.class).sum())
                        .from(StorageEnergy)
                        .where(StorageEnergy.equipment.id.in(equipmentIdList)
                                .and(StorageEnergy.setVal.eq("kccdl")))
                        .fetchOne();
                if (totalStorageEnergy == null) {
                    totalStorageEnergy = new BigDecimal(0.0);
                }
                jsonObject.put("totalStorageEnergy", totalStorageEnergy.setScale(2, BigDecimal.ROUND_HALF_UP));

                //总电量是项目中的总容量。
                QDiagnoseResultInfo Electricity = QDiagnoseResultInfo.diagnoseResultInfo;
                BigDecimal totalElectricity = diagnoseResultInfoRepository.query()
                        .select(Electricity.resultVal.castToNum(BigDecimal.class).sum())
                        .from(Electricity)
                        .where(Electricity.equipment.id.in(equipmentIdList)
                                .and(Electricity.setVal.eq("dcbrl"))
                                .and(Electricity.batch.ip.eq(stadningBookInfo.getProject().getIp()))
                        )
                        .fetchOne();
                if (totalElectricity == null) {
                    totalElectricity = new BigDecimal(0.0);
                }
                jsonObject.put("totalElectricity", totalElectricity.setScale(2, BigDecimal.ROUND_HALF_UP));

                Date lastDate = alarmRepository.query().select(QAlarm.alarm.createTime)
                        .from(QAlarm.alarm)
                        .where(QAlarm.alarm.equipmentId.in(equipmentIdList))
                        .orderBy(QAlarm.alarm.createTime.desc())
                        .limit(1)
                        .fetchOne();
                if (lastDate == null) { //容错性，万一此项目ES中没有值
                    lastDate = new Date();
                }
                Period period = Period.between(lastDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate(),
                        LocalDate.now());
                jsonObject.put("safeRunningDays", period.getDays());

                QAlarm qAlarm = QAlarm.alarm;
                Long count = alarmRepository.count(qAlarm.equipmentId.in(equipmentIdList)
                        .and(qAlarm.isValid.eq(1))
                        .and(qAlarm.state.lt(3)));
                if (count != null) {
                    jsonObject.put("faults", count);
                }
                result.add(jsonObject);
            }
            // 3) 填值
            String name = stadningBookInfo.getAccount().getName();
            String val = stadningBookInfo.getVal();
            switch (name) {
                case "经度":
                    jsonObject.put("longitude", val.isEmpty() ? "" : Double.parseDouble(val));
                    break;
                case "纬度":
                    jsonObject.put("latitude", val.isEmpty() ? "" : Double.parseDouble(val));
                    break;
                case "联系人":
                    jsonObject.put("contacts", val);
                    break;
                case "联系方式":
                    jsonObject.put("telphone", val);
                    break;
            }
        }
        return result;
    }

    /**
     * 概括转移电量
     *
     * @param type
     * @return
     */
    public JSONObject transferPower(String type) {
        JSONObject r;
        org.elasticsearch.common.collect.Tuple<LocalDateTime, LocalDateTime> dateRange = Utils.calDateRange(type, null, null);
        Date startDate = Date.from(dateRange.v1().toInstant(ZoneOffset.of("+8")));
        Date endDate = Date.from(dateRange.v2().minusDays(1).toInstant(ZoneOffset.of("+8")));
        String sql;
        switch (type) {
            case "week":
                r = Utils.getDayExitToday("week");
                sql = "SELECT DATE_FORMAT(`cal_time`, '%Y-%m-%d') AS time, sum(val)" +
                        " FROM esms_integration WHERE type=?  AND cal_time BETWEEN ? AND ?  GROUP BY time ORDER BY time";
                break;
            case "month":
                r = Utils.getDay("month");
                sql = "SELECT DATE_FORMAT(`cal_time`, '%Y-%m-%d') AS time, sum(val)" +
                        " FROM esms_integration WHERE type=? AND cal_time BETWEEN ? AND ? GROUP BY time ORDER BY time";
                break;
            case "year":
                r = Utils.getMonth();
                sql = "SELECT DATE_FORMAT(`cal_time`, '%Y-%m') AS time, sum(val)" +
                        " FROM esms_integration WHERE type=? AND cal_time BETWEEN ? AND ? GROUP BY time ORDER BY time";
                break;
            default:
                r = new JSONObject();
                return r;
        }

        Query qry = entityManager.createNativeQuery(sql);
        qry.setParameter(1, "fdl");
        qry.setParameter(2, startDate);
        qry.setParameter(3, endDate);
        List<Object[]> result = qry.getResultList();
        for (Object[] rs : result) {
            Double dval = Double.valueOf(rs[1].toString()) * 0.001; //概况中，值单位是MKh
            r.put((String) rs[0], dval);
        }
        return r;
    }
}
